
package cassandrawindow;

/**
 * @author ADMIN
 */
import com.mysql.jdbc.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.sql.Time;
import java.util.*;
import java.util.ArrayList;
/**
 *
 * @author ADMIN
 */
public class MysqlDataManagerMainCPM {

    private Connection conn = null;

    private String TABLE_BANNERS                = "ox_banners";
    private String V3_DOMAIN_DATE_VMASK         = "v3_domain_date_vmask";
    private String V3_BAN_DATE_VMASK            = "v3_ban_date_vmask";
    private String V3_ZONE_DATE_CPM             = "v3_zone_date_vmask";
    private String V3_CAM_DATE_CPM              = "v3_cam_date_vmask";
    private String V3_CAM_HOURLY_CPM            = "v3_cam_hourly_vmask";//V3_BAN_HOURLY_VMASK
    private String V3_BAN_HOURLY_VMASK          = "v3_ban_hourly_vmask";//    
    private String SYS_PARAMS_V4                = "000_sys_params_v4";//000_sys_params_v4
    
    private String V3_CAM_HOURLY                = "v3_cam_hourly";//
    public MysqlDataManagerMainCPM()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_MAIN_CPM , ACCOUNT.username_mysql_main_cpm, ACCOUNT.pass_mysql_main_cpm);//abc!@#
            //Debug.println("Connected to MySQL GFS Server database");
        }
        catch (Exception ex)
        {
            System.out.println("Couldn't connect to the MySQL database.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            System.out.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }
    }
    
    public int getCountCampaign()
    {
        int retVal = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ TABLE_BANNERS);
            while (rs.next())
            {
                retVal = rs.getInt(1);
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return retVal;
    }

    public List getCampaignIdBySlice(String date) throws InterruptedException
    {
        List infor = new ArrayList();
       /* int countAll = getCountCampaign();
        int div = (int) Math.floor(countAll/5000);
        
        for (int i = 0 ; i <= div ; i ++)
        {*/
            try
            {
                Statement st= (Statement)conn.createStatement();
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"' limit " + 5000*i + "," + 5000);
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"'ORDER BY campaignid DESC");

                /*String sql = "SELECT DISTINCT C.campaignid  FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` = C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' ))";
                */
                 String sql =  "SELECT DISTINCT A.campaignid FROM `ox_campaigns` A "+                                
                                //"INNER JOIN ox_banners B "+
                                //"ON B.campaignid = A.campaignid "+
                                "WHERE A.`revenue_type` IN (5,6,8,10) ";
                                //"AND '"+date+"' BETWEEN B.`activate` AND B.`expire` "+
                                //"GROUP BY A.`uid`";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    infor.add(rs.getString("A.campaignid"));
                }
                st.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage());
            }

            Thread.sleep(10);
        //}
        return infor;
    }

    public List getCampaignIdBySliceMobile(String date) throws InterruptedException
    {
        List infor = new ArrayList();
       /* int countAll = getCountCampaign();
        int div = (int) Math.floor(countAll/5000);

        for (int i = 0 ; i <= div ; i ++)
        {*/
            try
            {
                Statement st= (Statement)conn.createStatement();
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"' limit " + 5000*i + "," + 5000);
                //ResultSet rs = st.executeQuery("select DISTINCT campaignid from "+ TABLE_BANNERS + " WHERE DATE(updated) = '"+ date +"'ORDER BY campaignid DESC");

                String sql = "SELECT DISTINCT A.`campaignid` FROM `ox_banners` A"+
                   "INNER JOIN `ox_campaigns` B ON A.`campaignid`=B.`campaignid"+
                    "WHERE B.`revenue_type`=11 AND A.`status`= B.`status`= A.`deleted`=0";
                //Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    infor.add(rs.getString("A.campaignid"));
                }
                st.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage());
            }

            Thread.sleep(10);
        //}
        return infor;
    }

    public List getBannerIDByCampaignId(int campaignId,String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            // status =(1.8) là dang chay
            //(5,10) la cpc và cpc plus
            String sql = "SELECT DISTINCT C.bannerid FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE C.`campaignid`= "+ campaignId +" AND A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' ))";
            //Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return infor;
    }

    public Map getMaxValueDaybyBannerId(String date)
    {
        Map<Integer, Long> MaxValue = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT C.bannerid, A.maxValueDay FROM `ox_campaigns` A "+
                    "INNER JOIN `ox_banners` C ON A.`campaignid` =C.`campaignid`  "+
                    "LEFT JOIN `selfserving_inform` B ON (C.`campaignid`=B.`id` AND B.date='"+ date +"')"+
                    "LEFT JOIN `selfserving_inform` D ON (C.`bannerid` =D.`id` AND D.date='"+ date +"') "+
                    "WHERE A.`revenue_type` IN(5,10) AND (A.`isExpired` IN (0,3,4,5,10,11) OR ( B.`date`='"+ date +"' ))"+
                    "AND (C.`status` IN (1,8) OR (D.`date` = '"+ date +"' )) AND (C.`deleted`=0 OR (C.`deleted`=1 AND D.`date` ='"+ date +"' )) GROUP BY  C.bannerid,'"+ date +"'";
            //Debug.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                MaxValue.put(rs.getInt("C.bannerid"), rs.getLong("A.maxValueDay"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return MaxValue;
    }

    public List getBannerIDByCampaignIdMobile(int campaignId,String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "SELECT DISTINCT A.`bannerid` FROM `ox_banners` A"+
                   "INNER JOIN `ox_campaigns` B ON A.`campaignid`=B.`campaignid"+
                    "WHERE A.`campaignid`= "+ campaignId +" AND B.`revenue_type`=11 AND A.`status`= B.`status`= A.`deleted`=0";           
            
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getString("C.bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return infor;

    }

    public Map getAllBannerHasDomain(String date)
    {
        Map<String,String> mp=new HashMap<String, String>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            //ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = '"+ date +"' ORDER BY bannerid DESC");
            ResultSet rs = st.executeQuery("SELECT B.bannerid,B.list_all_site FROM ox_campaigns A INNER JOIN ox_banners B ON A.campaignid=B.campaignid WHERE DATE(A.updated) = '"+ date +"' AND A.revenue_type IN(5,10)");

            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_all_site"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return mp;
    }

    public List getValidBanner(String date)
    {
        List addInfo = new ArrayList();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT B.bannerid FROM ox_campaigns A INNER JOIN ox_banners B ON A.campaignid=B.campaignid WHERE DATE(A.updated) = '"+ date +"' AND A.revenue_type IN(5,10)");

            while (rs.next())
            {
                addInfo.add(rs.getString("bannerid"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return addInfo;
    }
    
    public Map getPriceByDomain()
    {        
        Map<String, Integer> PriceByDomain = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type`=1");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return PriceByDomain;
    }
    //get price with cpcplus
    public Map getPriceByDomainCPCPlus()
    {
        Map<String, Integer> PriceByDomain = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name, price FROM selfserving_price A WHERE A.`type`=5");

            while (rs.next())
            {
                PriceByDomain.put(rs.getString("name"), rs.getInt("price"));
            }
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return PriceByDomain;
    }

    
    public List getAllBanneridStoporPause(String dt)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT distinct id  FROM `selfserving_inform` A WHERE DATE = '"+ dt +"' and type in (4,16,17,19)");

            while (rs.next())
            {
                infor.add(rs.getInt("id"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        
        return infor;
    }
    
    public Map getAllUserActive()
    {
        Map<Integer, String> allUsers = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT user_id,balance,promotions,groupid FROM selfserving_users WHERE active = '1'");

            while (rs.next())
            {
                //if((rs.getInt("balance") > 0) ||(rs.getInt("promotions") > 0))
                //{
                    allUsers.put(rs.getInt("user_id"),rs.getString("balance")+"_"+rs.getString("promotions")+"_"+rs.getString("groupid"));
                //}
            }            
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return allUsers;
    }

    public Map getAllUserActiveCpcPlus()
    {
        Map<Integer, String> allUsers = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT user_id,balance,promotions FROM cpcplus_users_balances");

            while (rs.next())
            {
                //if((rs.getInt("balance") > 0) ||(rs.getInt("promotions") > 0))
                //{
                    allUsers.put(rs.getInt("user_id"),rs.getString("balance")+"_"+rs.getString("promotions"));
                //}
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return allUsers;
    }


    public Map getAllGroupID()
    {
        Map<Integer, Integer> allGroupId = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT groupid,typegroup FROM selfserving_users_group");

            while (rs.next())
            {               
                    allGroupId.put(rs.getInt("groupid"),rs.getInt("typegroup"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return allGroupId;
    }
    
    public Map getCampaignAndUserid(String date) throws InterruptedException
    {        
            Map<Integer, String> CampaignAndUserid = new HashMap<>();
            
            try
            {
                Statement st= (Statement)conn.createStatement();
                /*
                String sql = "SELECT uid,GROUP_CONCAT(cmp) FROM ( SELECT DISTINCT C.campaignid AS cmp, A.uid AS uid "+
                            "FROM `ox_campaigns` A " +
                            "INNER JOIN `ox_banners` C " +
                            "ON A.`campaignid` = C.`campaignid` "+
                            "LEFT JOIN `selfserving_inform` B "+
                            "ON (C.`campaignid` = B.`id` AND B.date = '"+date+"') "+
                            "LEFT JOIN `selfserving_inform` D "+
                            "ON (C.`bannerid` = D.`id` AND D.date = '"+date+"') "+
                            "WHERE   A.`revenue_type` IN (5, 10) "+
                            "AND (   A.`isExpired` IN (0, 3, 4, 5, 10, 11) "+
                            "OR (B.`date` = '"+date+"')) "+
                            "AND (C.`status` IN (1, 8) OR (D.`date` = '"+date+"')) "+
                            "AND (C.`deleted` = 0 OR (C.`deleted` = 1 AND D.`date` = '"+date+"'))) AS su "+
                            "GROUP BY uid";*/
                  String sql = "SELECT A.`uid` AS userid, GROUP_CONCAT(A.`campaignid`) "+
                                "FROM `ox_campaigns` A "+
                                //"INNER JOIN ox_banners B "+
                                //"ON B.campaignid = A.campaignid "+
                                "WHERE A.`revenue_type` IN (5,6,8,10) "+
                                //"AND '"+date+"' BETWEEN B.`activate` AND B.`expire` "+
                                "GROUP BY A.`uid`";
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignAndUserid.put(rs.getInt("userid"),rs.getString("GROUP_CONCAT(A.`campaignid`)"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage());
            }

            Thread.sleep(10);
        //}
        return CampaignAndUserid;
    }

    //lay nhung campaign thuoc cpc thuong
    public List getCampaignCPC() throws InterruptedException
    {        
            List CampaignCPC = new ArrayList();
            
            try
            {
                Statement st= (Statement)conn.createStatement();              
                  String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+                               
                                "WHERE revenue_type IN (5)";                                                                
                Debug.println(sql);

                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPC.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage());
            }

            Thread.sleep(10);
        //}
        return CampaignCPC;
    }

     //lay nhung campaign thuoc cpc plus
    public List getCampaignCPCPlus() throws InterruptedException
    {
            List CampaignCPCPlus = new ArrayList();
            try
            {
                Statement st= (Statement)conn.createStatement();
                String sql = "SELECT campaignid "+
                                "FROM `ox_campaigns` "+
                                "WHERE revenue_type IN (10)";
                Debug.println(sql);
                ResultSet rs = st.executeQuery(sql);
                while (rs.next())
                {
                    CampaignCPCPlus.add(rs.getLong("campaignid"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage());
            }

            Thread.sleep(10);
        //}
        return CampaignCPCPlus;
    }

    public List getValidDomain()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT DISTINCT domaingroup FROM selfserving_site_channel");

            while (rs.next())
            {
                infor.add(rs.getString("domaingroup"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return infor;
    }

    public void insertDataDomainData(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE_VMASK +"(campaignid, bannerid, domain, totalclick, totalview,realclick, realview,clickcharge,viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }

    }

    public void insertSliceDataCampaignAndBanner(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE_VMASK +"(campaignid,bannerid,totalclick,totalview,realclick,realview,clickcharge,viewcharge,uc,uv,MB,MT,MN,Other,dt,money) values " + value;
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public void insertDataZoneCPM(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE_CPM +"(campaignid, zoneid, bannerid, totalclick, totalview, realclick,realview, clickcharge, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }

    }

     public void insertDataCampaignDay(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_DATE_CPM +"(campaignid,totalclick,totalview,realclick,realview,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
    }

    public void insertDataCampaignDayCPM(String value) throws SQLException
    {
            String sql = "insert into employee (campaignid, totalclick, totalview, realclick, "
                    + "realview, clickcharge, viewcharge, dt, moneyc, moneyv) "
                    + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE "
                    + "campaignid=VALUES(campaignid),dt=VALUES(dt)";            
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
            final int batchSize = 1000;
            int count = 0;

            for (int i = 0 ;i < 1000; i++) {

                ps.setString(1, "1");
                ps.setString(2, "1");
                ps.setString(3, "1");
                ps.addBatch();

                if(++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            ps.close();
            conn.close();
    }

    public void insertDataCampainHourForCPC(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public void insertDataCampainHour(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_CPM +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public void insertDataBannerHour(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_HOURLY_VMASK +"(bannerid,clickhour,viewhour,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
    }

    public Map getClickByDomainDateCPM(String date)
    {
        Map<String, String> ClickByDomainDate = new HashMap<>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT campaignid,bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE_VMASK+" WHERE dt = '"+date+"' GROUP BY campaignid ,bannerid,dt");
            Debug.println("SELECT campaignid,bannerid,SUM(totalclick) AS totalclick,SUM(totalview) AS totalview,SUM(money) AS money FROM "+V3_DOMAIN_DATE_VMASK+" WHERE dt = '"+date+"' GROUP BY campaignid ,bannerid,dt");
            while (rs.next())
            {                
                ClickByDomainDate.put(rs.getString("campaignid")+"_"+rs.getString("bannerid")+"_"+date, rs.getLong("totalclick")+"_"+rs.getLong("totalview")+"_"+rs.getLong("money"));
            }
            
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return ClickByDomainDate;
    }

    public boolean UpdateTimeForCountMoney()
    {
        boolean check = false;
        try
        {
            Statement s = conn.createStatement();            
            String sql = "update "+ SYS_PARAMS_V4 +" set LastTimeCalc_vmask = NOW() where id = 1";            
            check = s.execute(sql);
            
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return check;
    }
}
